package dao;

import model.Sale;
import utils.DBUtil;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class SaleDAO {
    public boolean addSale(Sale sale) {
        String sql = "INSERT INTO sales (book_id, quantity, total_price, sale_date) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, sale.getBookId());
            pstmt.setInt(2, sale.getQuantity());
            pstmt.setDouble(3, sale.getTotalPrice());
            pstmt.setTimestamp(4, new Timestamp(sale.getSaleDate().getTime()));
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    
    public List<Sale> getAllSales() {
        List<Sale> sales = new ArrayList<>();
        String sql = "SELECT s.*, b.title FROM sales s JOIN books b ON s.book_id = b.id";
        
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                Sale sale = new Sale(
                    rs.getInt("id"),
                    rs.getInt("book_id"),
                    rs.getString("title"),
                    rs.getInt("quantity"),
                    rs.getDouble("total_price"),
                    rs.getTimestamp("sale_date")
                );
                sales.add(sale);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sales;
    }
    
    public List<Sale> getSalesByDateRange(Date startDate, Date endDate) {
        List<Sale> sales = new ArrayList<>();
        String sql = "SELECT s.*, b.title FROM sales s " +
                    "JOIN books b ON s.book_id = b.id " +
                    "WHERE s.sale_date BETWEEN ? AND ? " +
                    "ORDER BY s.sale_date";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setTimestamp(1, new Timestamp(startDate.getTime()));
            pstmt.setTimestamp(2, new Timestamp(endDate.getTime()));
            
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Sale sale = new Sale(
                    rs.getInt("id"),
                    rs.getInt("book_id"),
                    rs.getString("title"),
                    rs.getInt("quantity"),
                    rs.getDouble("total_price"),
                    rs.getTimestamp("sale_date")
                );
                sales.add(sale);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sales;
    }
    
    public Map<String, Double> getDailySalesStats(Date startDate, Date endDate) {
        Map<String, Double> dailyStats = new LinkedHashMap<>();
        String sql = "SELECT DATE(sale_date) as sale_day, SUM(total_price) as daily_total " +
                    "FROM sales WHERE sale_date BETWEEN ? AND ? " +
                    "GROUP BY DATE(sale_date) ORDER BY sale_day";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setTimestamp(1, new Timestamp(startDate.getTime()));
            pstmt.setTimestamp(2, new Timestamp(endDate.getTime()));
            
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                dailyStats.put(
                    rs.getString("sale_day"),
                    rs.getDouble("daily_total")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dailyStats;
    }
    
    public List<Sale> searchSales(String keyword) {
        List<Sale> sales = new ArrayList<>();
        String sql = "SELECT s.*, b.title FROM sales s " +
                    "JOIN books b ON s.book_id = b.id " +
                    "WHERE b.title LIKE ? " +
                    "ORDER BY s.sale_date DESC";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, "%" + keyword + "%");
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Sale sale = new Sale(
                    rs.getInt("id"),
                    rs.getInt("book_id"),
                    rs.getString("title"),
                    rs.getInt("quantity"),
                    rs.getDouble("total_price"),
                    rs.getTimestamp("sale_date")
                );
                sales.add(sale);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sales;
    }
} 